package com.situ.supermarket.dao;

import com.situ.supermarket.entity.Purchase;
import com.situ.supermarket.util.DBUtil;
import com.situ.supermarket.util.StringUtil;
import com.situ.supermarket.vo.PurchaseVO;

import java.util.List;

public class PurchaseDao {

    public Long count(String name, String employName) {
        String sql = "select count(1) from purchase ";
        if(StringUtil.isNotBlank(name) && StringUtil.isNotBlank(employName)){
            sql+=" where instr(name,?)>0 and instr(employName,?)>0";
            return DBUtil.executeCount(sql,name,employName);
        }
        if(StringUtil.isNotBlank(employName)){
            sql+=" where instr(employName,?)>0";
            return DBUtil.executeCount(sql,employName);
        }
        if(StringUtil.isNotBlank(name)){
            sql+=" where instr(name,?)>0 ";
            return DBUtil.executeCount(sql,name);
        }
        return DBUtil.executeCount(sql);
    }

    public List<PurchaseVO> select(Integer page, Integer limit, String name, String employeeName) {
        String sql = "select purchase.*,employee.name 'employeeName' from purchase left join employee on employee.id=purchase.employee_id";
        if(StringUtil.isNotBlank(name) && StringUtil.isNotBlank(employeeName)){
            sql+=" where instr(purchase.name,?)>0 and instr(employee.name,?)>0";
            sql+=" limit ?,?";
            return DBUtil.executeDQL(sql, PurchaseVO.class,name,employeeName,(page-1)*limit,limit);
        }
        if(StringUtil.isNotBlank(employeeName)){
            sql+=" where instr(employee.name,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,PurchaseVO.class,employeeName,(page-1)*limit,limit);
        }
        if(StringUtil.isNotBlank(name)){
            sql+=" where instr(purchase.name,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,PurchaseVO.class,name,(page-1)*limit,limit);
        }
        sql+=" limit ?,?";
        return DBUtil.executeDQL(sql,PurchaseVO.class,(page-1)*limit,limit);
    }

    public void delete(Integer id) {
        String sql = "delete from purchase where id=?";
        DBUtil.executeDML(sql,id);
    }

    public void insert(Purchase p) {
        String sql = "insert into purchase(name,suppliers,number,cost,incoming_date,employee_id) values(?,?,?,?,?,?)";
        DBUtil.executeDML(sql,p.getName(),p.getSuppliers(),p.getNumber(),p.getCost(),p.getIncomingDate(),p.getEmployeeId());
    }

    public void update(Purchase p) {
        String sql = "update purchase set name=?,suppliers=?,number=?,cost=?,incoming_date=?,employee_id=? where id=?";
        DBUtil.executeDML(sql,p.getName(),p.getSuppliers(),p.getNumber(),p.getCost(),p.getIncomingDate(),p.getEmployeeId(),p.getId());
    }

    public List<Purchase> selectById(Integer id) {
        String sql = "select * from purchase where id=?";
        return DBUtil.executeDQL(sql, Purchase.class,id);
    }

    public List<Purchase> selectId(Integer employeeId) {
        String sql = "select * from purchase where ";
        if(employeeId!=null){
            sql+="employee_id= "+employeeId;
        }
        return DBUtil.executeDQL(sql,Purchase.class);
    }
}
